<html>
<head>
<title>firstworks   Programming with SQL Relay using Zope</title>
<link href="../css/styles.css" rel="stylesheet">
</head>
<body>

<span class="heading1">Programming with SQL Relay using Zope</span><br><br>

<ul>
<li><a href="#installation">Installing ZSQLRelayDA</a></li>
<li><a href="#instance">Creating a ZSQLRelayDA instance</a></li>
<li><a href="#method">Creating a ZSQLMethod</a></li>
<li><a href="#lastinsertid">Getting the Last Insert ID in SQLite</a></li>
</ul>

<a name="installation"></a>
<span class="heading2">Installing ZSQLRelayDA</span><br><br>

<p>The Zope Database Adapter is called ZSQLRelayDA.  Once installed, you can 
run ZSQLMethods against it like any other Zope Database adapter.</p>

<p>These directions assume that your Zope installation is in /usr/zope
and that your Python libraries are installed in /usr/lib/python1.5</p>

<p>If these assumptions do not apply to you then substitute your installation
paths for the ones in this document.</p>

<p>To install the Zope adapter, first copy or move the src/api/zope/ZSQLRelayDA
directory of this distribution into the /usr/zope/lib/python/Products directory.
</p>

<p>Zope needs to know about the SQLClient python module as well. The most
portable way to make Zope aware of the module is to create a symbolic link 
from that directory to /usr/zope/lib/python/Products/ZSQLRelayDA/SQLRelay</p>

<p>The command:</p>

<blockquote>
<p>ln -s /usr/lib/python1.5/site-packages/SQLRelay /usr/zope/lib/python/Products/ZSQLRelayDA/SQLRelay</p>
</blockquote>

<p>should do the trick.</p>

<p>Now you're ready to use Zope and SQL Relay together.</p>

<a name="instance"></a>
<span class="heading2">Creating a ZSQLRelayDA Instance</span><br><br>

<p>After installing the ZSQLRelayDA, log into the Zope management interface.
Do not log in as the "emergency user" (usually superuser), if you have not
already, create another user and log in as that user.</p>

<p>At this time, make sure the SQL Relay instance you intend to connect
to is running.</p>

<p>Once you have logged in, the "Z SQLRelay Database Connection" product is 
available in the "Select type to add..." pulldown menu on the right.</p>

<p><img src="zope/management.gif"></p>

<p>To create an instance, select "Z SQLRelay Database Connection" from the
pulldown.  Replace the default Id and Title with meaningful names and
enter the database connection string.  Instructions describing the format of 
the database connection string is described at the bottom of the page.</p>

<p><img src="zope/instance.gif"></p>

<p>Click the "Add" button.  You'll see your newly created instance.</p>

<p><img src="zope/sqlrelay.gif"></p>

<br><br>
<a name="method"></a>
<span class="heading2">Creating a ZSQLMethod Instance</span><br><br>

<p>You can test the connection by clicking on the link to your recently created
connection and then on the "Test" tab at the top of the next page.  Type a query
into the text area.</p>

<p><img src="zope/test.gif"></p>

<p>Click the "Submit Query" button. You should now see your result set.</p>

<p><img src="zope/resultset.gif"></p>

<br><br>
<a name="method"></a>
<span class="heading2">Creating a ZSQLMethod</span><br><br>

<p>Now you can use your ZSQLRelayDA instance in ZSQLMethods.  Return to the Root
folder and select "Z SQL Method" from the "Select type to add..." pulldown menu
on the right.</p>

<p>Enter a meaningful id and title and select the id of your ZSQLRelayDA 
instance from the pulldown.  Fill in the "Arguments" and "Query Template" text 
areas.  If you need help with ZSQLMethods, click the "Help!" link in the top
right hand corner of the page.</p>

<p><img src="zope/zsqlmethod.gif"></p>

<p>Click the "Add" button below the text area.</p>

<p>You can test the method by clicking on the link to your recently created
method and then on the "Test" tab at the top of the next page.</p>

<p><img src="zope/testzsqlmethod.gif"></p>

<p>Click the "Submit Query" button. You should now see your result set.</p>

<p><img src="zope/zsqlmethodresultset.gif"></p>

<p>Now you can use your ZSQLMethod in other Zope objects.</p>

<a name="lastinsertid"></a>
<span class="heading2">Getting the Last Insert ID in SQLite</span><br><br>

<p>The SQLite API provides a function which returns the "last insert rowid";
the rowid of the most recent insert into the database or 0 if no inserts have 
ever been performed during the session.</p>

<p>SQL Relay supports fetching the last insert rowid from an SQLite database
as well.  You just have to run the query:

<blockquote>
<b>select last insert rowid</b>
</blockquote>

</body>
</html>
